#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''
@FileName  :database.py 
@desc      :
@Author    :Young
@IDE       :PyCharm
@Time      :2022-09-25 11:01
'''

import sqlite3

con = sqlite3.connect('./ticketDB1.db')
cur = con.cursor()

def database_init():
    '''
    数据库初始化
    :return: None
    '''
    sql = 'create table if not exists ticket(id integer primary key, IDUserName varchar(30), IDCardNumber text, isChecked integer)'
    cur.execute(sql)

def create_ticket_info(userName, userID):
    '''
    创建一条检票信息，存入数据库
    :param ticket_info:
    :return: True | False  是否插入数据成功
    '''
    sql = 'insert into ticket(`IDUserName`,`IDCardNumber`,`isChecked`) values(?,?,?)'
    try:
        cur.execute(sql,(userName, userID, 0))
        con.commit()
        print("当前检票数据插入数据库成功！")
    except Exception as e:
        print(e)
        con.rollback()  # 插入失败时回滚
        print('插入数据失败')


def check_ticket_info(ticket_ID):
    '''
    首先检测该票据是否属于已注册用户，若不是，则返回该票据卡号未注册,返回0
    若该票据信息已经注册，则检测ischecked是否为1， 若为1表示检测过，返回1，若为0，表示没有检测过，返回2
    :param ticket_info:
    :return: 1 | 2 | 3
    '''
    sql = 'select * from ticket where IDCardNumber= ?'
    cur.execute(sql,(ticket_ID,))
    result = cur.fetchone()

    if result:
        # print(111111)
        print(result)  #(1, '李明', '12F6D734', 0)
        print("当前卡号已经存在数据库中")
        #print("result3:",result[3])
        if(result[3] == 0):
            print("该票用户已注册，未检票")
            # 将该用户的isChecked修改为1
            sql1 = "update ticket set isChecked=1 where IDCardNumber=?"
            cur.execute(sql1,(ticket_ID,))
            cur.execute(sql, (ticket_ID,))
            result1 = cur.fetchone()
            print("result:",result1)
            return 2  #该票用户已注册，未检票
        else:
            print("该票用户已注册，检已票")
            return 1 #该用户已注册，已经检票
    else:
        print("当前卡号不在数据库中")
        return 0

def delete_ticket_info():
    '''
    清除数据库信息
    :param ticket_info:
    :return:None
    '''
    sql = 'delete from ticket'
    try:
        cur.execute(sql)
        print("删除数据库数据成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("删除数据库数据失败！")


def return_username_by_ID(ticket_ID):
    '''
    通过卡号查询对应用户
    :param ticket_ID:
    :return: userName
    '''
    sql = 'select * from ticket where IDCardNumber= ?'
    cur.execute(sql, (ticket_ID,))
    result = cur.fetchone()
    userName = result[1]

    return userName

def return_all_checked_info():
    '''
    返回所有的检票信息
    :return:result
    '''
    sql = "select * from ticket"
    cur.execute(sql)
    result = cur.fetchall()
    print("获取所有检票信息成功！")
    print(result)
    return result

def create_user_info():
    '''
    创建用户信息
    :return:
    '''
    create_ticket_info("YaLan Tao", "B9C8E1A2")
    create_ticket_info("ZhiHao Xu", "C3141A40")
    create_ticket_info('HanFei Pu', "E423F12A")
    # 展示数据库所有用户数据
    return_all_checked_info()

def close_cursor():
    '''
    释放数据库资源
    :return: None
    '''
    cur.close()  # 关闭游标
    con.close()  # 关闭连接

#test
if __name__ == '__main__':
    database_init()  #数据库初始化
    # create_user_info()  #创建用户信息
    # return_all_checked_info() #查看所有用户信息
    # return_username_by_ID("B9C8E1A2") #通过ID查询用户名称
    delete_ticket_info()
    return_all_checked_info()  # 查看所有用户信息
    close_cursor()

